package demo1.dao.sql;

import demo1.dao.Mapper.goodsMapper;
import demo1.pojo.goods;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class goodssql implements goodsMapper {

    @Override
    public List<goods> selectById(int id) throws Exception {

        Connection conn = conn();
        String sql = "select * from goods where id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, id);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods> goodsList = new ArrayList<>();
        goods goods;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goodsNum");//库存
            int goodsXiaoLiang = rs.getInt("goodsXiaoLiang");//销量
            int goodsID = rs.getInt("goodsID");
            String goodsMessage = rs.getString("goodsMessage");//商品介绍
            String goodsName = rs.getString("goodsName");//商品名称
            String goodsType = rs.getString("goodsType");//商品种类
            String statue = rs.getString("statue");//商品状态
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(goodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(id);
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setTime(time);
            goods.setGoodsID(goodsID);
            // 添加到列表中
            goodsList.add(goods);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsList;
    }

    @Override
    public void addGoods(int price,String goodsMessage,String goodsName,String goodsType,int goodsNum,int id) throws Exception {

        Connection conn = conn();
        String sql = "insert into goods(price,goodsMessage,goodsName,goodsNum,goodsXiaoLiang,goodsType,statue,id) values (?,?,?,?,?,?,?,?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, price);
        pstmt.setString(2, goodsMessage);
        pstmt.setString(3, goodsName);
        pstmt.setInt(4, goodsNum);
        pstmt.setInt(5, 0);
        pstmt.setString(6, goodsType);
        pstmt.setString(7, "下架");
        pstmt.setInt(8, id);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateGoodsName(String goodsName,int id,int goodsID) throws Exception {
        Connection conn = conn();
        String sql = "update goods set goodsName = ?where id = ? and goodsID = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, goodsName);
        pstmt.setInt(2, id);
        pstmt.setInt(3, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateGoodsMessage(String goodsMessage, int id,int goodsID) throws Exception {
        Connection conn = conn();
        String sql = "update goods set goodsMessage = ?where id = ? and goodsID = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, goodsMessage);
        pstmt.setInt(2, id);
        pstmt.setInt(3, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateGoodsprice(int price, int id,int goodsID) throws Exception {
        Connection conn = conn();
        String sql = "update goods set price = ? where id = ? and goodsID = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, price);
        pstmt.setInt(2, id);
        pstmt.setInt(3, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateGoodsType(String goodsType, int id, int goodsID) throws Exception {
        Connection conn = conn();
        String sql = "update goods set goodsType = ?where id = ? and goodsID = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, goodsType);
        pstmt.setInt(2, id);
        pstmt.setInt(3, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateGoodsNum(int goodsNum, int id,int goodsID) throws Exception {
        Connection conn = conn();
        String sql = "update goods set goodsNum = (?) where id = (?) and goodsID = (?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, goodsNum);
        pstmt.setInt(2, id);
        pstmt.setInt(3, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateGoodsNumByName(int goodsNum, String goodsName, String goodsMessage) throws Exception {
        Connection conn = conn();
        String sql = "update goods set goodsNum = (?) where goodsName = (?) and goodsMessage = (?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, goodsNum);
        pstmt.setString(2, goodsName);
        pstmt.setString(3, goodsMessage);

        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateGoodsXiaoLiangByName(int goodsXiaoLiang, String goodsName, String goodsMessage) throws Exception {
        Connection conn = conn();
        String sql = "update goods set goodsXiaoLiang = (?) where goodsName = (?) and goodsMessage = (?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, goodsXiaoLiang);
        pstmt.setString(2, goodsName);
        pstmt.setString(3, goodsMessage);

        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public ResultSet selectByAll(int id, int goodsID) throws Exception {
        Connection conn = conn();
        String sql = "select * from goods where id = ? and goodsID = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, id);
        pstmt.setInt(2, goodsID);
        //执行sql
        return pstmt.executeQuery();
    }

    @Override
    public void deleteGoods(int id, int goodsID) throws Exception {
        Connection conn = conn();
        String sql = "delete from goods where id = ? and goodsID = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, id);
        pstmt.setInt(2, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateGoodsStatue(String statue, int id, int goodsID) throws Exception {
        Connection conn = conn();
        String sql = "update goods set statue = ? where id = ? and goodsID = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, statue);
        pstmt.setInt(2, id);
        pstmt.setInt(3, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateGoodsStatueByName(String statue, String goodsName, String goodsMessage) throws Exception {
        Connection conn = conn();
        String sql = "update goods set statue = ? where  goodsName = ? and goodsMessage = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, statue);
        pstmt.setString(2, goodsName);
        pstmt.setString(3, goodsMessage);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateGoodsTime(Timestamp time, int id, int goodsID) throws Exception {
        Connection conn = conn();
        String sql = "update goods set time = ? where id = ? and goodsID = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setTimestamp(1, time);
        pstmt.setInt(2, id);
        pstmt.setInt(3, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public List<goods> selectPart(String goodsName) throws Exception {
        Connection conn = conn();
        String sql = "select * from goods where statue = ? and goodsName like ? ";//时间从早到晚,ORDER BY time desc从晚到早
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "上架");
        pstmt.setString(2, "%"+goodsName+"%");
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods> goodsList = new ArrayList<>();
        goods goods;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goodsNum");//库存
            int goodsXiaoLiang = rs.getInt("goodsXiaoLiang");//销量
            int goodsID = rs.getInt("goodsID");
            String goodsMessage = rs.getString("goodsMessage");//商品介绍
            String newgoodsName = rs.getString("goodsName");//商品名称
            String goodsType = rs.getString("goodsType");//商品种类
            String statue = rs.getString("statue");//商品状态
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(newgoodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(rs.getInt("id"));
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setTime(time);
            goods.setGoodsID(goodsID);
            // 添加到列表中
            goodsList.add(goods);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsList;
    }

    @Override
    public List<goods> selectAllEarly() throws Exception {
        Connection conn = conn();
        String sql = "select * from goods where statue = ? ORDER BY time ASC";//时间从早到晚,ORDER BY time desc从晚到早
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "上架");
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods> goodsList = new ArrayList<>();
        goods goods;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goodsNum");//库存
            int goodsXiaoLiang = rs.getInt("goodsXiaoLiang");//销量
            int goodsID = rs.getInt("goodsID");
            String goodsMessage = rs.getString("goodsMessage");//商品介绍
            String goodsName = rs.getString("goodsName");//商品名称
            String goodsType = rs.getString("goodsType");//商品种类
            String statue = rs.getString("statue");//商品状态
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(goodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(rs.getInt("id"));
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setTime(time);
            goods.setGoodsID(goodsID);
            // 添加到列表中
            goodsList.add(goods);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsList;
    }

    @Override
    public List<goods> selectAllLate() throws Exception {
        Connection conn = conn();
        String sql = "select * from goods where statue = ? ORDER BY time DESC";//时间从早到晚,ORDER BY time desc从晚到早
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "上架");
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods> goodsList = new ArrayList<>();
        goods goods;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goodsNum");//库存
            int goodsXiaoLiang = rs.getInt("goodsXiaoLiang");//销量
            int goodsID = rs.getInt("goodsID");
            String goodsMessage = rs.getString("goodsMessage");//商品介绍
            String goodsName = rs.getString("goodsName");//商品名称
            String goodsType = rs.getString("goodsType");//商品种类
            String statue = rs.getString("statue");//商品状态
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(goodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(rs.getInt("id"));
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setTime(time);
            goods.setGoodsID(goodsID);
            // 添加到列表中
            goodsList.add(goods);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsList;
    }

    @Override
    public List<goods> selectAllXiaoLiangHigh() throws Exception {
        Connection conn = conn();
        String sql = "select * from goods where statue = ? ORDER BY goodsXiaoLiang ASC";//时间从早到晚,ORDER BY time desc从晚到早
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "上架");
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods> goodsList = new ArrayList<>();
        goods goods;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goodsNum");//库存
            int goodsXiaoLiang = rs.getInt("goodsXiaoLiang");//销量
            int goodsID = rs.getInt("goodsID");
            String goodsMessage = rs.getString("goodsMessage");//商品介绍
            String goodsName = rs.getString("goodsName");//商品名称
            String goodsType = rs.getString("goodsType");//商品种类
            String statue = rs.getString("statue");//商品状态
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(goodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(rs.getInt("id"));
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setTime(time);
            goods.setGoodsID(goodsID);
            // 添加到列表中
            goodsList.add(goods);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsList;
    }

    @Override
    public List<goods> selectAllXiaoLiangLower() throws Exception {
        Connection conn = conn();
        String sql = "select * from goods where statue = ? ORDER BY goodsXiaoLiang DESC";//时间从早到晚,ORDER BY time desc从晚到早
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "上架");
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods> goodsList = new ArrayList<>();
        goods goods;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goodsNum");//库存
            int goodsXiaoLiang = rs.getInt("goodsXiaoLiang");//销量
            int goodsID = rs.getInt("goodsID");
            String goodsMessage = rs.getString("goodsMessage");//商品介绍
            String goodsName = rs.getString("goodsName");//商品名称
            String goodsType = rs.getString("goodsType");//商品种类
            String statue = rs.getString("statue");//商品状态
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(goodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(rs.getInt("id"));
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setTime(time);
            goods.setGoodsID(goodsID);
            // 添加到列表中
            goodsList.add(goods);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsList;
    }

    @Override
    public goods selectByGoodsId(int goodsId) throws Exception {
        Connection conn = conn();
        String sql = "select * from goods where goodsID = ? ";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, goodsId);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        goods goods = null;
        if (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goodsNum");//库存
            int goodsXiaoLiang = rs.getInt("goodsXiaoLiang");//销量
            int goodsID = rs.getInt("goodsID");
            String goodsMessage = rs.getString("goodsMessage");//商品介绍
            String goodsName = rs.getString("goodsName");//商品名称
            String goodsType = rs.getString("goodsType");//商品种类
            String statue = rs.getString("statue");//商品状态
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(goodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(rs.getInt("id"));
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setTime(time);
            goods.setGoodsID(goodsID);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goods;
    }

    @Override
    public int selectByGoodsByName(String goodsName, String goodsMessage) throws Exception {
        Connection conn = conn();
        String sql = "select * from goods where goodsName = ? and goodsMessage = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, goodsName);
        pstmt.setString(2, goodsMessage);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        int goodsNum = 0;
        if (rs.next()) {
            goodsNum = rs.getInt("goodsNum");
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsNum;
    }

    @Override
    public int selectByGoodsstatueByName(String goodsName, String goodsMessage) throws Exception {
        Connection conn = conn();
        String sql = "select * from goods where goodsName = ? and goodsMessage = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, goodsName);
        pstmt.setString(2, goodsMessage);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        int num = -1;
        String statue = null;
        if (rs.next()) {
            statue = rs.getString("statue");
        }
        if (Objects.equals(statue, "下架")){
            rs.close();
            pstmt.close();
            conn.close();
            return 0;
        }
        else {
            rs.close();
            pstmt.close();
            conn.close();
            return 1;
        }
    }

    @Override
    public int selectByGoodsXiaoLiangByName(String goodsName, String goodsMessage) throws Exception {
        Connection conn = conn();
        String sql = "select * from goods where goodsName = ? and goodsMessage = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, goodsName);
        pstmt.setString(2, goodsMessage);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        int goodsXiaoLiang = 0;
        if (rs.next()) {
            goodsXiaoLiang = rs.getInt("goodsXiaoLiang");
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsXiaoLiang;
    }
    public Connection conn()throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/project2";
        String user = "root";
        String password = "111214Cjj";

        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
}
